I'm getting the failure ORA-22806: not an object or REF
when querying
SELECT (CASE WHEN "SHAPE" IS NULL THEN NULL
ELSE ("SHAPE").SDO_POINT.X END)
FROM (SELECT null AS "SHAPE" FROM DUAL)
in Oracle. It appears Oracle is evaluating the else statement even though the case expression is true. Anyone know a way to make this work? Requirements are to return null if "SHAPE" is null, otherwise return ("SHAPE").SDO_POINT.X. Edit: This also needs to be done without modifying the subquery.
APC :
\nI forgot to mention another requirement - I can't modify the subquery\n\n@SStrong: this additional requirement boils down to: "my unmodified code has a bug: how can I remove the bug without modifying my code?"\nSo all you can is put in an intermediary wrapper around the subquery which handles NULL values without applying case, using @lukewoodward suggestion. Something like this:\nSELECT (CASE WHEN "SHAPE" IS NULL THEN NULL\n ELSE ("SHAPE").SDO_POINT.X END)\nFROM ( select case when "SHAPE" is null then cast(null as sdo_geometry) \n else "SHAPE" end as "SHAPE" \n from (SELECT null AS "SHAPE" FROM DUAL) --<-- your unmodifiable subquery\n)\n/\n",
2022-08-24T07:01:26
Luke Woodward :
Oracle isn't evaluating the ELSE statement, but it seems it is checking the types of columns and expressions in your query.\nI suspect that it starts type-checking your query by type-checking the subquery. The subquery is SELECT null AS "SHAPE" FROM DUAL. While type-checking this subquery Oracle doesn't know what the type the SHAPE column should be, so it chooses a type for it rather than make do without knowing the type for the time being and see if it can find out the real type later on.\nFor me (Oracle 18c XE), the error goes away if I cast null to SDO_GEOMETRY:\nSQL> SELECT (CASE WHEN "SHAPE" IS NULL THEN NULL\n 2 ELSE ("SHAPE").SDO_POINT.X END)\n 3 FROM (SELECT CAST(null AS SDO_GEOMETRY) AS "SHAPE" FROM DUAL);\n\n(CASEWHEN"SHAPE"ISNULLTHENNULLELSE("SHAPE").SDO_POINT.XEND)\n-----------------------------------------------------------\n\n\nSQL>\n\nI am guessing that your database has a table with a SHAPE column of type SDO_GEOMETRY, but I could of course be wrong.",
2022-08-23T21:54:16